/*
 * SqlFinder.java
 *
 * Created on 2005年10月9日, 上午10:24
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package com.sinodata.bsm.cicp.finder;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import com.sinodata.bsm.cicp.util.DBConnectionUtil;

/**
 * @author tangli
 */
public class SqlFinder extends Finder {

    private static final Logger logger = Logger.getLogger(SqlFinder.class);

    /**
     *  Creates a new instance of SqlFinder
     */
    public SqlFinder() {
    }

    @Override
    public List<String[]> find(String ip, Map params) {
        List<String[]> list = new ArrayList<String[]>();
        String[] oneResource = null;
        //获取数据库类型
        String type = (String) params.get("TYPE");
        if (type == null) {
            type = "oracle";
        }
        //获取数据库实例名
        String dbname = (String) params.get("DB");
        if (dbname == null) {
            logger.error("DB should not be null.");
            return null;
        }
        //获取sql语句
        String sql = (String) params.get("SQL");
        if (sql == null) {
            logger.error("SQL should not be null.");
            return null;
        }
        //获取资源名称
        String name = (String) params.get("NAME");
        if (name == null) {
            logger.error("NAME should not be null");
            return null;
        }
        //获取资源描述
        String descript = (String) params.get("DESC");
        if (descript == null) {
            logger.error("DESC should not be null.");
            return null;
        }
        //获取资源internal id
        String itlid = (String) params.get("ID");
        if (itlid == null) {
            logger.error("ITLID should not be null.");
            return null;
        }
        //执行sql语句并解析
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionUtil.getInstance().getConnection(type, ip, dbname);
            if (type.equals("sybase")) {
                conn.setCatalog(dbname); //此处dbname读取catalog值
            }
        } catch (Exception e) {
            logger.error("failed to exec sql:" + e.getMessage());
            return null;
        }
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData rsm = rs.getMetaData();
            int count = rsm.getColumnCount();
            while (rs.next()) {
                try {
                    oneResource = new String[3 + count];
                    list.add(oneResource);
                    oneResource[0] = replace(itlid, rs);
                    oneResource[1] = replace(name, rs);
                    oneResource[2] = replace(descript, rs);
                    for (int i = 3; i < oneResource.length; i++) {
                        oneResource[i] = rs.getObject(i - 2).toString();
                    }
                } catch (Exception e) {
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
            DBConnectionUtil.getInstance().returnConnection(conn);
        }
    }

    /**
     * 该函数的功能是将itlid,name,和descript中的"$+数字"如"$1"替换成rs中的第一列
     * @param str
     * @param rs
     * @return
     */
    private String replace(String str, ResultSet rs) {
        Pattern pattern = Pattern.compile("^.*\\$(\\d+).*$");
        Matcher matcher = pattern.matcher(str);
        if (matcher.find()) {
            int index = Integer.parseInt(matcher.group(1));
            try {
                str = str.replace("$" + index, rs.getObject(index).toString());
            } catch (SQLException e) {
            }
        }
        return str;
    }
}
